﻿using System.Text;
using YookingBizWQW.Class.Public;
using YookingSvrDBCall.Class.DBLogic;
using YookingSvrDBCall.Class.DBPub;
using YookingInterfaceAli.Class.OpenApi;

namespace YookingBizWQW.Class.HomeVx
{
    public class clsHomeV1
    {
        //注释：王庆文 2019年12月28日========================================================
        //统计当前系统的商户分析数据、部门店铺分析数据、用户人数分析数据、活跃用户分析数据
        public string GetOlapCountInfo(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strSQLCondition = "1=1"; //查询条件
 
            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 1)
            {
                strSQLCondition = strArray[0];
            }

            #endregion

            #region 生成查询记录的SQL语句

            clsDBSpace myType = new clsDBSpace();
            string strDBType = myType.GetDBType("Data");

            StringBuilder sbQuerySQL = new StringBuilder();
            if (strDBType == "ORACLE")
            {
                #region 生成符合ORACLE语法格式的SQL语句

                sbQuerySQL.AppendLine(" SELECT UNITCOUNT, ");
                sbQuerySQL.AppendLine("        round(decode(UNITCOUNT, 0, 0, UnitXzCount / UNITCOUNT), 4) * 100 || '%' UNITRATE, ");
                sbQuerySQL.AppendLine("        DEPTCOUNT, ");
                sbQuerySQL.AppendLine("        round(decode(DEPTCOUNT, 0, 0, DeptXzCount / DEPTCOUNT), 4) * 100 || '%' DEPTRATE, ");
                sbQuerySQL.AppendLine("        PERSONCOUNT, ");
                sbQuerySQL.AppendLine("        round(decode(PERSONCOUNT, 0, 0, PersonXzCount / PERSONCOUNT), 4) * 100 || '%' PERSONRATE, ");
                sbQuerySQL.AppendLine("        LOGINCOUNT, ");
                sbQuerySQL.AppendLine("        round(decode(LOGINCOUNT, 0, 0, LoginXzCount / LOGINCOUNT), 4) * 100 || '%' LOGINRATE ");
                sbQuerySQL.AppendLine("   FROM (SELECT count(DISTINCT UnitCode) UNITCOUNT ");
                sbQuerySQL.AppendLine("           FROM SA_ORG_UNITINFO ");
                sbQuerySQL.AppendLine("          WHERE nvl(SYS_CANCELDATETIME, '0') = '0') a ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UnitCode) UnitXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_UNITINFO ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     to_char(trunc(sysdate, 'month'), 'yyyy-mm-dd') || ");
                sbQuerySQL.AppendLine("                     ' 00:00:00' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     to_char(add_months(trunc(sysdate, 'month'), 1) - 1, ");
                sbQuerySQL.AppendLine("                             'yyyy-mm-dd') || ' 23:59:59') b ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT DeptCode) DEPTCOUNT ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_DEPARTMENT ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0') c ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT DeptCode) DeptXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_DEPARTMENT ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     to_char(trunc(sysdate, 'month'), 'yyyy-mm-dd') || ");
                sbQuerySQL.AppendLine("                     ' 00:00:00' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     to_char(add_months(trunc(sysdate, 'month'), 1) - 1, ");
                sbQuerySQL.AppendLine("                             'yyyy-mm-dd') || ' 23:59:59') d ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT PersonID) PERSONCOUNT ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_PERSONINFO ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0') e ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT PersonID) PersonXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_PERSONINFO ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     to_char(trunc(sysdate, 'month'), 'yyyy-mm-dd') || ");
                sbQuerySQL.AppendLine("                     ' 00:00:00' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     to_char(add_months(trunc(sysdate, 'month'), 1) - 1, ");
                sbQuerySQL.AppendLine("                             'yyyy-mm-dd') || ' 23:59:59') f ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UserName) LOGINCOUNT ");
                sbQuerySQL.AppendLine("                FROM OP_USERLOGINLOG ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0') g ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UserName) LoginXzCount ");
                sbQuerySQL.AppendLine("                FROM OP_USERLOGINLOG ");
                sbQuerySQL.AppendLine("               WHERE nvl(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     to_char(trunc(sysdate, 'month'), 'yyyy-mm-dd') || ");
                sbQuerySQL.AppendLine("                     ' 00:00:00' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     to_char(add_months(trunc(sysdate, 'month'), 1) - 1, ");
                sbQuerySQL.AppendLine("                             'yyyy-mm-dd') || ' 23:59:59') h ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");

                #endregion
            }
            else if (strDBType == "MYSQL")
            {
                #region 生成符合MYSQL语法格式的SQL语句

                sbQuerySQL.AppendLine(" SELECT UNITCOUNT, ");
                sbQuerySQL.AppendLine("        CONCAT(round(if(UNITCOUNT=0,0,UnitXzCount/UNITCOUNT), 2) * 100,'%') UNITRATE, ");
                sbQuerySQL.AppendLine("        DEPTCOUNT, ");
                sbQuerySQL.AppendLine("        CONCAT(round(if(DEPTCOUNT=0,0,DeptXzCount/DEPTCOUNT), 2) * 100,'%') DEPTRATE, ");
                sbQuerySQL.AppendLine("        PERSONCOUNT, ");
                sbQuerySQL.AppendLine("        CONCAT(round(if(PERSONCOUNT=0,0,PersonXzCount/PERSONCOUNT), 2) * 100,'%') PERSONRATE, ");
                sbQuerySQL.AppendLine("        LOGINCOUNT, ");
                sbQuerySQL.AppendLine("        CONCAT(round(if(LOGINCOUNT=0,0,LoginXzCount/LOGINCOUNT), 2) * 100,'%') LOGINRATE ");
                sbQuerySQL.AppendLine("   FROM (SELECT count(DISTINCT UnitCode) UNITCOUNT ");
                sbQuerySQL.AppendLine("           FROM SA_ORG_UNITINFO ");
                sbQuerySQL.AppendLine("          WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0') a ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UnitCode) UnitXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_UNITINFO ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(DATE_FORMAT(now(), '%m'), 'yyyy-mm-dd'),' 00:00:00') ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(ADDDATE(DATE_FORMAT(now(), '%m'), INTERVAL 1 MONTH) - 1,'yyyy-mm-dd'),' 23:59:59')) b ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT DeptCode) DEPTCOUNT ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_DEPARTMENT ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0') c ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT DeptCode) DeptXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_DEPARTMENT ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(DATE_FORMAT(now(), '%m'), 'yyyy-mm-dd'),' 00:00:00') ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(ADDDATE(DATE_FORMAT(now(), '%m'), INTERVAL 1 MONTH) - 1,'yyyy-mm-dd'),' 23:59:59')) d ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT PersonID) PERSONCOUNT ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_PERSONINFO ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0') e ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT PersonID) PersonXzCount ");
                sbQuerySQL.AppendLine("                FROM SA_ORG_PERSONINFO ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(DATE_FORMAT(now(), '%m'), 'yyyy-mm-dd'),' 00:00:00') ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(ADDDATE(DATE_FORMAT(now(), '%m'), INTERVAL 1 MONTH) - 1,'yyyy-mm-dd'),' 23:59:59')) f ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UserName) LOGINCOUNT ");
                sbQuerySQL.AppendLine("                FROM OP_USERLOGINLOG ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0') g ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");
                sbQuerySQL.AppendLine("   LEFT JOIN (SELECT count(DISTINCT UserName) LoginXzCount ");
                sbQuerySQL.AppendLine("                FROM OP_USERLOGINLOG ");
                sbQuerySQL.AppendLine("               WHERE IFNULL(SYS_CANCELDATETIME, '0') = '0' ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime >= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(DATE_FORMAT(now(), '%m'), 'yyyy-mm-dd'),' 00:00:00') ");
                sbQuerySQL.AppendLine("                 AND Sys_InputDateTime <= ");
                sbQuerySQL.AppendLine("                     CONCAT(date_format(ADDDATE(DATE_FORMAT(now(), '%m'), INTERVAL 1 MONTH) - 1,'yyyy-mm-dd'),' 23:59:59')) h ");
                sbQuerySQL.AppendLine("     ON 1 = 1 ");

                #endregion
            }

            #endregion

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            strRtData = myDB.YookingExecuteJson("Data", sbQuerySQL.ToString());

            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2019年12月28日========================================================
        //统计分析近期活跃用户的系统登录情况，包括时间、用户、IP等
        public string GetUserLoginLog(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strSQLCondition = "1=1"; //查询条件

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 1)
            {
                strSQLCondition = strArray[0];
            }

            #endregion

            #region 生成查询记录的SQL语句

            int iMaxCount = 20; //限制读取的数据行数

            clsDBSpace myType = new clsDBSpace();
            string strDBType = myType.GetDBType("Data");
            if (strDBType == "ORACLE")
            {
                strSQLCondition = "rownum<=" + iMaxCount + " order by t.LOGINTIME desc";
            }
            else if (strDBType == "MYSQL")
            {
                strSQLCondition = "1=1 order by t.LOGINTIME desc LIMIT 0," + iMaxCount;
            }
            else if (strDBType == "SQLSERVER")
            {
                strSQLCondition = "1=1 order by t.LOGINTIME desc";
            }

            StringBuilder sbQuerySQL = new StringBuilder();
            sbQuerySQL.AppendLine(" SELECT ");
            if (strDBType == "SQLSERVER")
            {
                sbQuerySQL.AppendLine(" 	top " + iMaxCount);
            }
            sbQuerySQL.AppendLine(" 	t.LOGINTIME, ");
            sbQuerySQL.AppendLine(" 	t.USERNAME AS LOGINUSER, ");
            sbQuerySQL.AppendLine(" 	t.ROUTEIP, ");
            sbQuerySQL.AppendLine(" 	t.ROUTEDATA ");
            sbQuerySQL.AppendLine(" FROM ");
            sbQuerySQL.AppendLine(" (select * from OP_USERLOGINLOG order by logintime desc) t ");
            sbQuerySQL.AppendLine(" WHERE " + strSQLCondition); //只查最后iMaxCount条记录

            #endregion

            clsDbSQLLogic myDB = new clsDbSQLLogic(); //实例化数据库操作类
            strRtData = myDB.YookingExecuteJson("Data", sbQuerySQL.ToString());

            return strRtData; //数据库查询结果
        }

        //注释：王庆文 2019年12月28日========================================================
        //调用淘宝的免费IP地址解析转换接口，得到IP所属国家、地区、城市、行政区划代码等信息
        public string GetIPJsonAttr(string strB64RequestData)
        {
            string strRtData = "no data";

            //先进行解密，然后在进行中文解码，最后得到明文的请求数据
            string strRequestData = clsHandleData.DeUrlRequest(strB64RequestData);

            #region 将strRequestData包含的各种参数一一解析出来

            string strIPAddress = "127.0.0.1"; //IP地址

            //这些参数用★分隔开，客户端与服务器端要一致
            string[] strArray = strRequestData.Split('★');
            if (strArray.Length >= 1)
            {
                strIPAddress = strArray[0];
            }

            #endregion

            clsTaoBaoIP myIP = new clsTaoBaoIP();
            strRtData = myIP.GetIPJsonAttribute(strIPAddress);

            return strRtData; //json格式的ip转换结果
        }




    }
}
